<%@ page import="java.sql.Statement" import="java.sql.ResultSet" import="java.sql.Connection" %> 
<%@ page import="java.sql.*,java.util.*,java.text.*,java.text.SimpleDateFormat" %>
<%@page import="connection.DBConnection"%>
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
    pageEncoding="ISO-8859-1"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Opening Balance Report</title>
</head>
<body>
<h2 align="center">Opening Balance Report</h2>
<table border=2 align="center">
<tr style="background-color:#4b545f"><th><font color=white>Sr no.</font></th><th><font color=white>Name of the Ledger Account</font></th><th><font color=white>Group</font></th><th><font color=white>Amount</font></th></tr>
<%
DBConnection dbc=new DBConnection();
Connection con=dbc.getNewConnection();
Statement st = null;
ResultSet rs = null;
String name1="",date="",dtecreate="",intime="";
int count=0;
double tot=0.0,am=0.0,am1=0.0,am2=0.0,am3=0.0,ctot=0.0,stot=0.0,ltot=0.0,btot=0.0;
	System.out.println("MySQL Connect Example.");
	

	  

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select cname,copen,ccode from customermaster");
while(rs.next()){
	name1=rs.getString(1);
	
	 am=rs.getDouble(2);
	 String cd=rs.getString(3);
	 ctot=ctot+am;
	 count++;
	%>


<tr><td><%=count%></td><td><a href="http://localhost:8081/BOA_Web/OpeningBalanceServlet?vouchernumber=<%=cd%>&name=<%=name1 %>&group=customer"><%=name1 %></a></td><td>Sundry Debtors</td><td><%=am %></td></tr>


<%}} 
catch(Exception e) {} %>


<%-- 
<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select cname,dateedited from customermaster where dateedited between '"+dte+"' and '"+dte1+"';");
while(rs.next()){
	name1=rs.getString(1);
	if((rs.getString(2)!=null && rs.getString(2)!=""))
	  {
	  dtecreate = rs.getString(2).toString().substring(0,rs.getString(2).toString().indexOf("."));
	  try
	 {
	 now = new  SimpleDateFormat("yyyy-MM-dd hh:mm:ss").parse( dtecreate);
	  sdf = new SimpleDateFormat("hh:mm:ss, dd-MM-yyyy");
	  intime = sdf.format(now);
	  }
	  catch (Exception e)
	  {
	  e.getMessage();
	  }
	  }
	%>


<tr><td>Customer Creation</td><td> Customer <a href="#"><%=name1 %></a> Updated</td><td><%=intime %></td></tr>


<%}} 
catch(Exception e) {} %>

--%>
<%

try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select sname,sopen,scode from suppliermaster");
while(rs.next()){
	name1=rs.getString(1);
	
	 am1=rs.getDouble(2);
	 
	 String sc=rs.getString(3);
	 
	 stot=stot+am1;
	 count++;
	%>


<tr><td><%=count%></td><td><a href="http://localhost:8081/BOA_Web/OpeningBalanceServlet?vouchernumber=<%=sc%>&name=<%=name1 %>&group=supplier"><%=name1 %></a></td><td>Sundry Creditors</td><td><%=am1 %></td></tr>







<%}} 
catch(Exception e) {} %>

<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select gname,ggroup,gbal,gcode from generalledger2;");
	  while(rs.next()){
			name1=rs.getString(1);
			String name2=rs.getString(2);
			 am2=rs.getDouble(3);
				String gc=rs.getString(4);
			 ltot=ltot+am2;
			 count++;
			%>


		<tr><td><%=count%></td><td><a href="http://localhost:8081/BOA_Web/OpeningBalanceServlet?vouchernumber=<%=gc %>&name<%=name1%>&group=general"><%=name1 %></a></td><td><%=name2 %></td><td><%=am2 %></td></tr>



<%}} 
catch(Exception e) {} %>


<%
try
{
	  st=con.createStatement() ;
	  rs=st.executeQuery("select bname,bopen,micr from bankmaster ;");
	  while(rs.next()){
			name1=rs.getString(1);
			
			 am3=rs.getDouble(2);
			 String mi=rs.getString(3);
			 btot=btot+am3;
			 count++;
			%>


		<tr><td><%=count%></td><td><a href="http://localhost:8081/BOA_Web/OpeningBalanceServlet?vouchernumber=<%=mi%>&name=<%=name1 %>&group=bank"><%=name1 %></a></td><td>Bank</td><td><%=am3 %></td></tr>

<%}} 
catch(Exception e) {} %>
<% tot=ctot+stot+ltot+btot; %>
<tr><td></td><td>Grand Total</td><td></td><td><%=tot %></td></tr>
</table>
<br>
<center><input type="button" value="Print this page" onClick="window.print()"></center>

<br><br>
<center><a href="HomePage.jsp">Back to Home Page</a></center>
</body>
</html>